![[SQL]クロス集計と、テンポラリテーブルを使用したクエリの分割](https://devio2023-media.developers.io/wp-content/uploads/2015/07/sql.png)
[SQL]クロス集計と、テンポラリテーブルを使用したクエリの分割
はじめに
前回にも書きましたが、SQLの特徴として(PL/SQLなどは除いて)
- 変数に代入して次の処理に行くことができない
- メソッドやクラスに処理を分割できない
ことが挙げられます。なのでサブクエリなどを使っていくと、一つのクエリが長くなりがちです。これについては「テンポラリテーブル」を使用して一時的なテーブルにデータを保持させることで、一つ一つのクエリを短く・複数に分割することが可能です。
またデータの表示方法として以下の様な2次元の表というのは非常に分かりやすいかと思います。
| 2005年05月 | 2005年06月 | 2005年07月 | |
|---|---|---|---|
| タイトル１ | 10人 | 20人 | 30人 |
| タイトル２ | 40人 | 50人 | 60人 |
| タイトル３ | 70人 | 80人 | 90人 |
このような縦・横方向に項目を持つ集計を「クロス集計」と言います。が、SQLで単純にSELECTした場合、取得した項目を縦方向に保持してしまいます。
今回は「テンポラリテーブル」を使用してクエリを短くすることと、上記のような「クロス集計」をSQLで実現する方法について書きたいと思います。
今回行いたいこと
サンプルデータ
今回は以下のPostgreSQLのサンプルデータベースを使いました。DVDのレンタルショップを題材としたデータのようです。
PostgreSQL Sample Database
リンクを開いてもらうとER図が載っているので参照してください。
抽出するデータについて
rentalテーブルに保持しているレンタル履歴データとinventoryテーブル・filmテーブルを結合し、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しました。そして抽出したデータを、縦方向にはフィルムID(film_id)・タイトル(title)を、横方向には年月を持つクロス集計とします。注意点としては、各月のユニークユーザ数は月毎・フィルム毎のユニークユーザ数の合計とならないことです。
では、これらをSQLにて実現する手順についてです。
テンポラリテーブルの作成
まずはテンポラリテーブルの作成についてです。上記にも書きましたが、これを行う目的は一時的なテーブルにデータを保持することでクエリを短く・単純にすることです。今回はデータの抽出に必要なテーブルの結合、及び項目の絞り込みを予め行い、テンポラリテーブルに格納したいと思います。では、先に書いたrentalテーブル・inventoryテーブル・filmテーブルを結合し、テンポラリテーブルに格納します。
CREATE TEMPORARY TABLE tmp_rantal_film AS
SELECT
r.rental_id
,to_char(r.rental_date, 'YYYY-MM') rental_month
,r.inventory_id
,r.customer_id
,i.film_id
,f.title
FROM
rental r
INNER JOIN
inventory i
ON
r.inventory_id = i.inventory_id
INNER JOIN
film f
ON
i.film_id = f.film_id
;
上記の3テーブルを結合し、必要な項目を抽出しています。また月毎のデータが必要であるため、「rental_month」という列名で予めレンタルした年月を取得するようにしています。この結果作成されるテンポラリテーブルは以下の通りです。
# tmp_rantal_filmテーブル rental_id rental_month inventory_id customer_id film_id title 361 2005-05 6 587 1 "Academy Dinosaur" 972 2005-05 2 411 1 "Academy Dinosaur" 1033 2005-05 14 25 3 "Adaptation Holes" 465 2005-05 20 261 4 "Affair Prejudice" 995 2005-05 17 150 4 "Affair Prejudice" 552 2005-05 23 106 5 "African Egg" (中略) 2117 2005-06 2 170 1 "Academy Dinosaur" 1210 2005-06 7 345 1 "Academy Dinosaur" 3201 2005-06 6 597 1 "Academy Dinosaur" 1427 2005-06 14 100 3 "Adaptation Holes" 2090 2005-06 17 197 4 "Affair Prejudice" 1716 2005-06 20 24 4 "Affair Prejudice" (以降略)
実際はもっと件数が多いのですが、分かりやすくするため一部を抜粋しております。このテンポラリテーブルからデータを抽出します。
SELECT
sub.*
FROM
(
SELECT
rental_month
,film_id
,title
,count(DISTINCT customer_id)
FROM
tmp_rantal_film
GROUP BY
rental_month
,film_id
,title
UNION
SELECT
rental_month
,NULL as film_id
,NULL as title
,count(DISTINCT customer_id)
FROM
tmp_rantal_film
GROUP BY
rental_month
) sub
ORDER BY
sub.rental_month
,sub.film_id
;
真ん中のサブクエリで、月毎・フィルム毎のユニークユーザ数と、各月のユニークユーザ数を抽出しています。このサブクエリに外側でORDER BYを掛けて、年月・film_idで並び替えています。この結果は以下の通りです。
rental_month film_id title count 2005-05 1 "Academy Dinosaur" 2 2005-05 3 "Adaptation Holes" 1 2005-05 4 "Affair Prejudice" 2 2005-05 5 "African Egg" 1 2005-05 NULL NULL 520 (中略) 2005-06 1 "Academy Dinosaur" 3 2005-06 3 "Adaptation Holes" 1 2005-06 4 "Affair Prejudice" 2 2005-06 5 "African Egg" 2 2005-06 NULL NULL 590 (以降略)
このままでも求めたい値は算出できているのですが、今回はこの結果を元にクロス集計を行います。なので値を算出するクエリとクロス集計を作るクエリを分けるため、この結果もテンポラリテーブルに登録します。以下がテンポラリテーブルを作るSQLとなります。
CREATE TEMPORARY TABLE tmp_rantal_unique_user AS
SELECT
sub.*
FROM
(
SELECT
rental_month
,film_id
,title
,count(DISTINCT customer_id)
FROM
tmp_rantal_film
GROUP BY
rental_month
,film_id
,title
UNION
SELECT
rental_month
,NULL as film_id
,NULL as title
,count(DISTINCT customer_id)
FROM
tmp_rantal_film
GROUP BY
rental_month
) sub
ORDER BY
sub.rental_month
,sub.film_id
;
先に紹介したSELECT文に「CREATE TEMPORARY TABLE・・・」を追加しただけですね。
クロス集計
最後にクロス集計です。実はクロス集計は以前の記事で書いた「縦持ちのデータを横持ちに入れ替える」と同じやり方となります。以下の様なSQLとなります。
SELECT
film_id
,COALESCE(title, title, '月次ユニークユーザ') AS title
,max(case rental_month when '2005-05' then count else null end) AS ２００５／０５
,max(case rental_month when '2005-06' then count else null end) AS ２００５／０６
,max(case rental_month when '2005-07' then count else null end) AS ２００５／０７
,max(case rental_month when '2005-08' then count else null end) AS ２００５／０８
,max(case rental_month when '2006-02' then count else null end) AS ２００６／０２
FROM
tmp_rantal_unique_user
GROUP BY
film_id
,title
ORDER BY
film_id
;
結果は以下の通りです。
film_id title ２００５／０５ ２００５／０６ ２００５／０７ ２００５／０８ ２００６／０２ 1 "Academy Dinosaur" 2 3 9 9 NULL 2 "Ace Goldfinger" NULL NULL 2 4 1 3 "Adaptation Holes" 1 1 4 6 NULL 4 "Affair Prejudice" 2 2 12 6 1 5 "African Egg" 1 2 5 3 1 NULL 月次ユニークユーザ 520 590 599 599 158
一番最初に示した2次元の表に近づいたかと思います。ただしこの方法の欠点としては、年月を動的に増やすことができないことです。また年月の表現に全角文字を使用していますが(使用したデータベースが半角数値からカラム名を始めることができないため)、ここも個人的には気になるところです(私がやったことですが・・・)。
まとめ
テンポラリテーブルを使用してSQLを分割し、クロス集計を行う例について見てきました。バッチ処理等では長いSQLが多くなりがちだと思いますが、その場合はテンポラリテーブルの導入を考えてもいいかもしれません。またクロス集計についても、以前書いたSQLによる縦持ち・横持ちを入れ替えることの実例として見て頂ければ幸いです。
参考文献
今回の内容については、以下の本を参考にさせて頂きました。ありがとうございました。
10年戦えるデータ分析入門
